/**
 * 
 */
package com.nari.heatshare.dao.springjdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;

import com.nari.coreframe.dao.springjdbc.AbstractDaoImpl;
import com.nari.coreframe.dao.springjdbc.extendcore.ResultSetMapHandler;
import com.nari.heatshare.utils.queryvo.AssignMeterDataCustomizedQuery;
import com.nari.heatshare.utils.queryvo.AssignMeterDataStatisExtendQuery;
import com.nari.platform.date.CalendarUtils;
import com.nari.platform.model.PageCond;
import com.nari.platform.model.ResultWithPageCond;
import com.nari.platform.sqlfunc.mysql.DateFunctionUtils4MySQL;
import com.nari.platform.utils.CommercialHighPrecisionArith;
import com.nari.platform.utils.StringUtils;
import com.nari.rescontroller.dao.IResourceAccessControlDao;

/**
 * 
 * @author Xiaopeng
 * @date 2014-12-9
 */
public class AssignMeterDataJdbcDaoImpl extends AbstractDaoImpl 
		implements IAssignMeterDataJdbcDao {
	private Logger logger = Logger.getLogger(AssignMeterDataJdbcDaoImpl.class);
	private IResourceAccessControlDao iResourceAccessControlDao;

	/**
	 * 分页查询分配表数据定制查询列表
	 * @param projectUID
	 * @param communityUID
	 * @param buildingUID
	 * @param recordTimeStart
	 * @param recordTimeEnd
	 * @param pageCond
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-12-9
	 * @deprecated 这个方法效率太低,结合索引的查询不行,需要优化 {@link AssignMeterDataJdbcDaoImpl#queryCustomizedQueryPage(String, String, String, Date, Date, PageCond)}
	 */
	public ResultWithPageCond<AssignMeterDataCustomizedQuery> queryCustomizedQueryPage_back(String projectUID, 
			String communityUID, String buildingUID, Date recordTimeStart, 
			Date recordTimeEnd, PageCond pageCond) throws Exception {
		//抄表日期范围限定
		Date startTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTimeStart));
		String startTimeStr = CalendarUtils.parseDateTimeToString(startTime);
		Date endTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTimeEnd));
		String endTimeStr = CalendarUtils.parseDateTimeToString(endTime);
		String querySQL = "select am.t_building_id as buildingUID," +
				 				 DateFunctionUtils4MySQL.dateSqlField2StringFunc("inner_grep.t_record_time")+" as recordDate," +
				 				 "count(inner_grep.t_seriousNum) as assignMeterDataCounter," +
				 				 "max(inner_grep.read_time) as readTime," +
				 				 "am.t_community_id as communityUID," +
				 				 "am.t_project_id as projectUID " +
				 		  "from (select inner_amd.t_seriousNum as t_seriousNum," +
				 		  			   "inner_amd.t_record_time as t_record_time," +
				 		  			   "inner_amd.read_time as read_time " +
				 		  	    "from p_assign_meter_data inner_amd " +
				 		  	    "where inner_amd.t_record_time between '"+startTimeStr+"' and '"+endTimeStr+"') inner_grep, p_assign_meter_info am " +
				 		  "where 1=1";
		//权限资源匹配
		String authorityRes = iResourceAccessControlDao.queryAuthorityProjResSql();
		if(!"".equals(authorityRes)) { //非管理员用户授权项目权限
			List<Object> projectUIDList = this.jdbcTemplate.queryForList(authorityRes, Object.class);
			String formatProjectUIDList = StringUtils.formatIDsByList(projectUIDList);
			formatProjectUIDList = StringUtils.formatIDListWithSyntax(formatProjectUIDList, ",", "'");
			querySQL += " and am.t_project_id in("+formatProjectUIDList+")";
		}
		//设置查询字段
		if(projectUID!=null && !"".equals(projectUID)) {
			querySQL += " and am.t_project_id='"+projectUID+"'";
		}
		if(communityUID!=null && !"".equals(communityUID)) {
			querySQL += " and am.t_community_id='"+communityUID+"'";
		}
		if(buildingUID!=null && !"".equals(buildingUID)) {
			querySQL += " and am.t_building_id='"+buildingUID+"'";
		}
		//设置表关联
		querySQL += " and inner_grep.t_seriousNum=am.t_serious_num";
		//定制查询分组
		querySQL += " group by am.t_building_id,"+DateFunctionUtils4MySQL.dateSqlField2StringFunc("inner_grep.t_record_time")+",am.t_community_id,am.t_project_id " +
				"having count(inner_grep.t_seriousNum)>0";
		//结果集个数查询
		String couterSQL = "select count(*) from ("+querySQL+") inner_counter";
		logger.debug("couterSQL:"+couterSQL);
		System.out.println("couterSQL:"+couterSQL);
		int total = this.jdbcTemplate.queryForInt(couterSQL);
		//定制查询排序及分页
		querySQL += " order by recordDate desc,am.t_building_id asc " +
				"limit "+pageCond.getStart()+","+pageCond.getLimit();
		logger.debug("querySQL:"+querySQL);
		System.out.println("querySQL:"+querySQL);
		//封装查询结果
		final List<AssignMeterDataCustomizedQuery> customizedQueryList = new ArrayList<AssignMeterDataCustomizedQuery>();
		this.getJdbcTemplate().query(querySQL, new RowCallbackHandler(){
			public void processRow(ResultSet rs) throws SQLException {
				try {
					AssignMeterDataCustomizedQuery customizedQuery = new AssignMeterDataCustomizedQuery();
					customizedQuery.setProjectUID(rs.getString("projectUID"));
					customizedQuery.setCommunityUID(rs.getString("communityUID"));
					customizedQuery.setBuildingUID(rs.getString("buildingUID"));
					customizedQuery.setRecordDate(rs.getString("recordDate"));
					//上传数据记录数
					customizedQuery.setAssignMeterDataCounter(rs.getInt("assignMeterDataCounter"));
					//楼栋定制的上传时间
					customizedQuery.setReadTime(rs.getTimestamp("readTime"));
					//成功上传数
					Date recordDate = CalendarUtils.parseStringToDate(customizedQuery.getRecordDate());
					int successUploadCounter = countBuildingSuccessUpload(customizedQuery.getBuildingUID(), recordDate,customizedQuery.getReadTime());
					customizedQuery.setSuccessUploadCounter(successUploadCounter);
					//分配表档案数
					int assignMeterNum = countAmNum4Building(customizedQuery.getBuildingUID());
					customizedQuery.setAssignMeterNum(assignMeterNum);
					//分配表数据的上传率
					double uploadPercent = CommercialHighPrecisionArith.divide(successUploadCounter * 100, 
							customizedQuery.getAssignMeterNum(), 5);
					customizedQuery.setUploadPercent(uploadPercent);
					customizedQueryList.add(customizedQuery);
				} catch (Exception e) {
					logger.error(e.getMessage());
					throw new SQLException(e.getMessage());
				}
			}
		});
		//构造返回类型
		pageCond.setTotal(total);
		ResultWithPageCond<AssignMeterDataCustomizedQuery> customizedQueryPage = new ResultWithPageCond<AssignMeterDataCustomizedQuery>();
		customizedQueryPage.setList(customizedQueryList);
		customizedQueryPage.setPageCond(pageCond);
		return customizedQueryPage;
	}
	
	public ResultWithPageCond<AssignMeterDataCustomizedQuery> queryCustomizedQueryPage(final String projectUID, 
			final String communityUID, final String buildingUID, Date recordTimeStart, 
			Date recordTimeEnd, PageCond pageCond) throws Exception {
		ResultWithPageCond<AssignMeterDataCustomizedQuery> customizedQueryPage = new ResultWithPageCond<AssignMeterDataCustomizedQuery>();
		Date startTime = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTimeStart));
		String startTimeStr = CalendarUtils.parseDateTimeToString(startTime);
		Date endTime = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTimeEnd));
		String endTimeStr = CalendarUtils.parseDateTimeToString(endTime);
		String querySQL ="SELECT COUNT(p.t_seriousNum) as assignMeterDataCounter,MAX(read_time) as readTime,DATE_FORMAT(t_record_time,'%Y-%m-%d') as recordDate , "
				+"temp.t_building_id as buildingUID,temp.t_project_id as projectUID,temp.t_community_id as communityUID  "
				+"FROM p_assign_meter_data  p JOIN  "
				+"(  "
				+"Select t_serious_num,t_project_id, t_community_id, t_building_id FROM p_assign_meter_info WHERE 1=1 ";
		//权限资源匹配
		String authorityRes = iResourceAccessControlDao.queryAuthorityProjResSql();
		//如果是管理员,且条件都为空则返回空集合
		if(!"".equals(authorityRes)) { //非管理员用户授权项目权限
			List<Object> projectUIDList = this.jdbcTemplate.queryForList(authorityRes, Object.class);
			String formatProjectUIDList = StringUtils.formatIDsByList(projectUIDList);
			formatProjectUIDList = StringUtils.formatIDListWithSyntax(formatProjectUIDList, ",", "'");
			querySQL += " and t_project_id in("+formatProjectUIDList+")";
		}
		else {	
			if(org.apache.commons.lang3.StringUtils.isBlank(projectUID)&&org.apache.commons.lang3.StringUtils.isBlank(buildingUID)&&org.apache.commons.lang3.StringUtils.isBlank(communityUID)) {
				//应该将所有的加起来 然后判断最后是否为空
				customizedQueryPage.setPageCond(pageCond);
				return customizedQueryPage;
			}
		}
		//设置查询字段
		if(projectUID!=null && !"".equals(projectUID)) {
			querySQL += " and t_project_id='"+projectUID+"'";
		}
		if(communityUID!=null && !"".equals(communityUID)) {
			querySQL += " and t_community_id='"+communityUID+"'";
		}
		if(buildingUID!=null && !"".equals(buildingUID)) {
			querySQL += " and t_building_id='"+buildingUID+"'";
		}
		querySQL = querySQL
				+" )  AS temp "
				+" ON p.t_seriousNum = temp.t_serious_num "
				+" WHERE "
				+" p.t_record_time BETWEEN '"+startTimeStr+"' AND '"+endTimeStr+"' "
				+" GROUP BY DATE_FORMAT(t_record_time,'%Y-%m-%d') ,buildingUID,projectUID,communityUID "
				+" ORDER BY t_record_time DESC ";
		String couterSQL = "select count(*) from ("+querySQL+") inner_counter";
		querySQL += "limit "+pageCond.getStart()+","+pageCond.getLimit();
		int total = this.jdbcTemplate.queryForInt(couterSQL);
		pageCond.setTotal(total);
		//封装查询结果
		//临时楼栋分配表数量缓存 防止过度查询数据库
		final AssignMeterDataCustomizedQuery cacheQuery = new AssignMeterDataCustomizedQuery();
		final List<AssignMeterDataCustomizedQuery> customizedQueryList = new ArrayList<AssignMeterDataCustomizedQuery>();
		this.getJdbcTemplate().query(querySQL, new RowCallbackHandler(){
			public void processRow(ResultSet rs) throws SQLException {
				try {
					AssignMeterDataCustomizedQuery customizedQuery = new AssignMeterDataCustomizedQuery();
					customizedQuery.setProjectUID(rs.getString("projectUID"));
					customizedQuery.setCommunityUID(rs.getString("communityUID"));
					customizedQuery.setBuildingUID(rs.getString("buildingUID"));
					customizedQuery.setRecordDate(rs.getString("recordDate"));
					//上传数据记录数
					customizedQuery.setAssignMeterDataCounter(rs.getInt("assignMeterDataCounter"));
					//楼栋定制的上传时间
					customizedQuery.setReadTime(rs.getTimestamp("readTime"));
					//成功上传数
					Date recordDate = CalendarUtils.parseStringToDate(customizedQuery.getRecordDate());
					//以三天内上传数进行统计
					int successUploadCounter = countBuildingSuccessUpload(customizedQuery.getBuildingUID(), recordDate,customizedQuery.getReadTime());
					customizedQuery.setSuccessUploadCounter(successUploadCounter);
					//分配表档案数
					//先查询当前的BuildingUID 是不是相同
					String querybuildingUID = customizedQuery.getBuildingUID();
					int assignMeterNum = 0;
					 if(org.apache.commons.lang3.StringUtils.equals(cacheQuery.getBuildingUID(), querybuildingUID)) {
						 assignMeterNum = cacheQuery.getAssignMeterNum();
					 }else {
						 assignMeterNum = countAmNum4Building(querybuildingUID);
						 cacheQuery.setAssignMeterNum(assignMeterNum);
						 cacheQuery.setBuildingUID(querybuildingUID);
					}
					customizedQuery.setAssignMeterNum(assignMeterNum);
					//分配表数据的上传率
					double uploadPercent = CommercialHighPrecisionArith.divide(successUploadCounter * 100, 
							customizedQuery.getAssignMeterNum(), 5);
					customizedQuery.setUploadPercent(uploadPercent);
					customizedQueryList.add(customizedQuery);
				} catch (Exception e) {
					logger.error(e.getMessage());
					throw new SQLException(e.getMessage());
				}
			}
		});
		customizedQueryPage.setList(customizedQueryList);
		customizedQueryPage.setPageCond(pageCond);
		return customizedQueryPage;
	}
	
	/**
	 * 查询单个楼栋的上传数据定制资料
	 * @param buildingUID
	 * @param recordDate
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-2-4
	 */
	public AssignMeterDataCustomizedQuery queryBuildingCustomizedQuery(String buildingUID, 
			Date recordDate) throws Exception {
		Date recordDateStart = CalendarUtils.getMinCalBasedSrcCal(recordDate);
		//三天上传率
		Calendar instance = Calendar.getInstance();
		String recordDateStartStr = CalendarUtils.parseDateTimeToString(recordDateStart);
		Date recordDateEnd = CalendarUtils.getMaxCalBasedSrcCal(recordDate);
		String recordDateEndStr = CalendarUtils.parseDateTimeToString(recordDateEnd);
		String querySQL = "select am.t_building_id as buildingUID," +
						   		  DateFunctionUtils4MySQL.dateSqlField2StringFunc("amd.t_record_time")+" as recordDate," +
						         "count(amd.t_seriousNum) as assignMeterDataCounter," +
						         "max(amd.read_time) as readTime," +
						         "am.t_community_id as communityUID," +
						         "am.t_project_id as projectUID " +
					      "from p_assign_meter_data amd,p_assign_meter_info am " +
					      "where am.t_building_id='"+buildingUID+"' " +
					      "and amd.t_record_time between '"+recordDateStartStr+"' and '"+recordDateEndStr+"' " +
					      "and amd.t_seriousNum=am.t_serious_num";
		logger.debug("querySQL:"+querySQL);
		System.out.println("querySQL:"+querySQL);
		return this.jdbcTemplate.queryForObject(querySQL, new RowMapper<AssignMeterDataCustomizedQuery>() {
			public AssignMeterDataCustomizedQuery mapRow(ResultSet rs, int index) throws SQLException {
				try {
					ResultSetMapHandler<AssignMeterDataCustomizedQuery> mapper = new ResultSetMapHandler<AssignMeterDataCustomizedQuery>();
					List<AssignMeterDataCustomizedQuery> buildingCustomizedQueryList = mapper.mapResultSet2Object(rs, AssignMeterDataCustomizedQuery.class);
					if(buildingCustomizedQueryList!=null && buildingCustomizedQueryList.size()>0) {
						AssignMeterDataCustomizedQuery customizedQuery = buildingCustomizedQueryList.get(0);
						//成功上传数
						Date recordDate = CalendarUtils.parseStringToDate(customizedQuery.getRecordDate());
						int successUploadCounter = countBuildingSuccessUpload(customizedQuery.getBuildingUID(), recordDate,customizedQuery.getReadTime());
						customizedQuery.setSuccessUploadCounter(successUploadCounter);
						//分配表档案数
						int assignMeterNum = countAmNum4Building(customizedQuery.getBuildingUID());
						customizedQuery.setAssignMeterNum(assignMeterNum);
						//分配表数据的上传率
						double uploadPercent = CommercialHighPrecisionArith.divide(successUploadCounter * 100, 
								customizedQuery.getAssignMeterNum(), 5);
						customizedQuery.setUploadPercent(uploadPercent);
						return customizedQuery;
					}
				} catch (Exception e) {
					logger.error(e.getMessage());
					throw new SQLException(e.getMessage());
				}
				return null;
			}
		});
	}
	
	/**
	 * 查询楼栋所属的分配表档案数
	 * @param buildingUID
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-2-4
	 */
	private int countAmNum4Building(String buildingUID) throws Exception {
		//分配表档案数
		String querySQL = "select count(*) from p_assign_meter_info am " +
				"where am.t_building_id='"+buildingUID+"'";
		logger.debug("querySQL:"+querySQL);
		System.out.println("querySQL:"+querySQL);
		return this.getJdbcTemplate().queryForInt(querySQL);
	}
	
	/**
	 * 统计楼栋的分配表数据的成功上传数
	 * 成功上传数认定条件：
	 * (1)隶属于该楼栋和抄表日期；
	 * (2)楼栋数据的上传日期取所有集中器上传数据中上传日期的最大值
	 * (3)分配表数据上传日期与楼栋数据的上传日期一致；
	 * (4)与上传最大日期相差3天之内的数据都算上传
	 * @param buildingUID
	 * @param recordDate
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-2-4
	 * @deprecated 效率比较低 使用其重载的方法
	 */
	public int countBuildingSuccessUpload(String buildingUID, Date recordDate) throws Exception {
		//抄表日期格式化
		Date recordDateStart = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordDate));
		String recordDateStartStr = CalendarUtils.parseDateTimeToString(recordDateStart);
		Date recordDateEnd = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordDate));
		String recordDateEndStr = CalendarUtils.parseDateTimeToString(recordDateEnd);
		String querySQL = "select outer_query.counter " +
				   "from (select "+DateFunctionUtils4MySQL.dateSqlField2StringFunc("amd.read_time")+" as readTime," +
				   		 "count(*) as counter " +
				   		 "from p_assign_meter_data amd,p_assign_meter_info am " +
				   		 "where am.t_building_id='"+buildingUID+"' " +
				   		 "and amd.t_record_time between '"+recordDateStartStr+"' and '"+recordDateEndStr+"' " +
				   		 "and amd.t_seriousNum=am.t_serious_num " +
				   		 "group by "+DateFunctionUtils4MySQL.dateSqlField2StringFunc("amd.read_time")+") outer_query " +
				   "where outer_query.readTime=(select max(inner_query.readTime) " +
				   							   "from (select "+DateFunctionUtils4MySQL.dateSqlField2StringFunc("amd.read_time")+" as readTime " +
				   							   		 "from p_assign_meter_data amd,p_assign_meter_info am " +
				   							   		 "where am.t_building_id='"+buildingUID+"' " +
				   							   		 "and amd.t_record_time between '"+recordDateStartStr+"' and '"+recordDateEndStr+"' " +
				   							   		 "and amd.t_seriousNum=am.t_serious_num " +
				   							   		 "group by "+DateFunctionUtils4MySQL.dateSqlField2StringFunc("amd.read_time")+") inner_query)";
		logger.debug("querySQL:"+querySQL);
		System.out.println("querySQL:"+querySQL);
		return getJdbcTemplate().queryForInt(querySQL);
	}
	
	
	/**
	 * 统计楼栋的分配表数据的成功上传数
	 * 成功上传数认定条件：
	 * (1)隶属于该楼栋和抄表日期；
	 * (2)楼栋数据的上传日期取所有集中器上传数据中上传日期的最大值
	 * (3)分配表数据上传日期与楼栋数据的上传日期一致；
	 * (4)与上传最大日期相差3天之内的数据都算上传
	 * @param buildingUID
	 * @param recordDate
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-2-4
	 */
	public int countBuildingSuccessUpload(String buildingUID, Date recordDate,Date maxReadTime) throws Exception {
		//抄表日期格式化
		Date recordDateStart = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordDate));
		String recordDateStartStr = CalendarUtils.parseDateTimeToString(recordDateStart);
		Date recordDateEnd = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordDate));
		String recordDateEndStr = CalendarUtils.parseDateTimeToString(recordDateEnd);
		String maxReadTimeStr = CalendarUtils.parseDateTimeToString(maxReadTime);
		//查询上传数
		
		String querySQL = "SELECT COUNT(1)   FROM "
				+"(  SELECT DISTINCT p.t_seriousNum  FROM p_assign_meter_data  p JOIN  (  "
				+"Select t_serious_num "
				+"FROM p_assign_meter_info WHERE 1=1 "
				+"	AND t_building_id = '"+buildingUID+"' "
				+") "
				+" AS temp "
				+"ON p.t_seriousNum = temp.t_serious_num "
				+"WHERE "
				+"p.t_record_time BETWEEN '"+recordDateStartStr+"' AND '"+recordDateEndStr+"' "
				+"AND ABS(TO_DAYS('"+maxReadTimeStr+"')-TO_DAYS(p.read_time))<3 "
				+") Q";
		
		return getJdbcTemplate().queryForInt(querySQL);
	}
	
	
	/**
	 * 查询批次任务的上传时间
	 * @param concentratorNo
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-2-3
	 */
	public Date queryReadTimeByUploadBatch(String concentratorNo, Date recordTime) throws Exception {
		//抄表时间格式化
		Date recordTimeStart = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		String recordTimeStartStr = CalendarUtils.parseDateTimeToString(recordTimeStart);
		Date recordTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		String recordTimeEndStr = CalendarUtils.parseDateTimeToString(recordTimeEnd);
		String querySQL = "select max(amd.read_time) as batchReadTime " +
				"from p_assign_meter_data amd " +
				"where amd.t_concentrator_no='"+concentratorNo+"' " +
				"and amd.t_record_time between '"+recordTimeStartStr+"' and '"+recordTimeEndStr+"'";
		logger.debug("querySQL:"+querySQL);
		System.out.println("querySQL:"+querySQL);
		return this.jdbcTemplate.queryForObject(querySQL, Date.class);
	}
	
	/**
	 * 查询与批次上传的上传时间不匹配的分配表数据ID
	 * @param concentratorNo
	 * @param recordTime
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-12-28
	 */
	public String queryNonMatchReadTime4Batch(String concentratorNo, Date recordTime) throws Exception {
		//抄表时间格式化
		Date recordTimeStart = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		String recordTimeStartStr = CalendarUtils.parseDateToString(recordTimeStart);
		Date recordTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(recordTime));
		String recordTimeEndStr = CalendarUtils.parseDateTimeToString(recordTimeEnd);
		//批次任务的上传时间
		Date batchReadTime = this.queryReadTimeByUploadBatch(concentratorNo, recordTime);
		Date batchReadTimeStart = CalendarUtils.getMinCalBasedSrcCal(CalendarUtils.getInstance(batchReadTime));
		String batchReadTimeStartStr = CalendarUtils.parseDateTimeToString(batchReadTimeStart);
		Date batchReadTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(CalendarUtils.getInstance(batchReadTime));
		String batchReadTimeEndStr = CalendarUtils.parseDateTimeToString(batchReadTimeEnd);
		String querySQL = "select amd.ID from p_assign_meter_data amd " +
				"where amd.t_concentrator_no='"+concentratorNo+"' " +
				"and amd.t_record_time between '"+recordTimeStartStr+"' and '"+recordTimeEndStr+"' " +
				"and amd.read_time not between '"+batchReadTimeStartStr+"' and '"+batchReadTimeEndStr+"'";
		logger.debug("querySQL:" + querySQL);
		System.out.println("querySQL:" + querySQL);
		//查询分配表数据ID列表
		List<Object> amdUIDList = this.jdbcTemplate.queryForList(querySQL, Object.class);
		return StringUtils.formatIDsByList(amdUIDList);
	}
	
	/**
	 * 统计分配表数据一个上传批次的成功上传数
	 * 成功上传数认定条件：
	 * (1)隶属于当前设定的上传批次，即具备相同的集中器号和抄表日期；
	 * (2)分配表数据上传日期与该批次上传日期一致；
	 * (3)抄表数据在档案中存在对应记录
	 * @param concentratorNo 集中器号
	 * @param recordTime 抄表日期
	 * @param readTime 上传时间
	 * @param dataType 数据类型
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-1-13
	 */
	public int countSuccessUploadNumReferBatch(String concentratorNo, Date recordTime, 
			Date readTime, Integer dataType) throws Exception {
		//抄表日期限定
		Date recordTimeStart = CalendarUtils.getMinCalBasedSrcCal(recordTime);
		String recordTimeStartStr = CalendarUtils.parseDateTimeToString(recordTimeStart);
		Date recordTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(recordTime);
		String recordTimeEndStr = CalendarUtils.parseDateTimeToString(recordTimeEnd);
		//上传日期限定
		//将开始日期设置为 readTime之前的2天
		Calendar instance = Calendar.getInstance();
		instance.setTime(readTime);
		instance.roll(Calendar.DAY_OF_YEAR, -2);
		Date starttime = instance.getTime();//将在最大readTime之前的同一批次的都算上
		Date readTimeStart = CalendarUtils.getMinCalBasedSrcCal(starttime);
		String readTimeStartStr = CalendarUtils.parseDateTimeToString(readTimeStart);
		
		Date readTimeEnd = CalendarUtils.getMaxCalBasedSrcCal(readTime);
		String readTimeEndStr = CalendarUtils.parseDateTimeToString(readTimeEnd);
		String querySQL = "select count(DISTINCT amd.t_seriousNum) from p_assign_meter_data amd " +
						  "where amd.t_concentrator_no='"+concentratorNo+"' " +
						  "and amd.t_record_time between '"+recordTimeStartStr+"' and '"+recordTimeEndStr+"' " +
						  "and amd.read_time between '"+readTimeStartStr+"' and '"+readTimeEndStr+"' " +
						  "and amd.t_seriousNum in(select am.t_serious_num " +
						  						  "from p_assign_meter_info am,base_concentrator_info con " +
						  						  "where am.t_concentrator_id=con.ID " +
						  						  "and con.t_concentrator_no='"+concentratorNo+"')";
		//数据类型
		if(dataType != null) {
			querySQL += " and amd.t_data_type="+dataType;
		}
		logger.debug("querySQL:" + querySQL);
		System.out.println("querySQL:" + querySQL);
		//查询成功上传数
		return this.jdbcTemplate.queryForInt(querySQL);
	}
	
	/**
	 * 通过数据文件唯一标识查询对应的分配表数据任务批次信息
	 * @param dataFileID
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-1-17
	 */
	public List<Map<String, String>> queryUploadBatchByDataFileUID(String dataFileUID) throws Exception {
		String querySQL = "select amd.t_concentrator_no as concentratorNo," +
							      DateFunctionUtils4MySQL.dateSqlField2StringFunc("amd.t_record_time")+" as recordTime " +
					      "from p_assign_meter_data amd " +
					      "where amd.data_file_id='"+dataFileUID+"' " +
					      "group by amd.t_concentrator_no,"+DateFunctionUtils4MySQL.dateSqlField2StringFunc("amd.t_record_time");
		final List<Map<String, String>> uploadBatchList = new ArrayList<Map<String,String>>();
		logger.debug("querySQL:" + querySQL);
		System.out.println("querySQL:" + querySQL);
		this.jdbcTemplate.query(querySQL, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				Map<String, String> uploadBatch = new HashMap<String, String>();
				uploadBatch.put("concentratorNo", rs.getString("concentratorNo"));
				uploadBatch.put("recordTime", rs.getString("recordTime"));
				uploadBatchList.add(uploadBatch);
			}
		});
		return uploadBatchList;
	}
	
	/**
	 * 根据分配表数据批次UID查询其对应的档案资料扩展信息
	 * @param meterDataStatisUIDList 分配表数据批次UID列表
	 * 格式：XXX,XXX,XXX
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2015-1-25
	 */
	public Map<String, AssignMeterDataStatisExtendQuery> queryAmdStatisExtend(String meterDataStatisUIDList) throws Exception {
		//分配表表号列表格式化
		String meterDataStaisUIDListFormat = StringUtils.formatIDListWithSyntax(meterDataStatisUIDList, ",", "'");
		String querySQL = "select amds.ID as meterDataStatisUID," +
								 "amds.t_concentrator_no as concentratorNo," +
								 "b.t_building_no as buildingCode," +
								 "b.t_building_area as buildingArea " +
						  "from p_assign_meter_data_statistics amds," +
						  	   "base_concentrator_info con," +
						  	   "base_building_info b " +
						  "where amds.ID in("+meterDataStaisUIDListFormat+") " +
						  "and amds.t_concentrator_no=con.t_concentrator_no " +
						  "and con.t_building_id=b.ID";
		logger.debug("querySQL:"+querySQL);
		System.out.println("querySQL:" + querySQL);
		//封装查询结果
		final Map<String, AssignMeterDataStatisExtendQuery> extendQueryMap = new HashMap<String, AssignMeterDataStatisExtendQuery>();
		this.jdbcTemplate.query(querySQL, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				try {
					ResultSetMapHandler<AssignMeterDataStatisExtendQuery> mapper = new ResultSetMapHandler<AssignMeterDataStatisExtendQuery>();
					//注意：这里已完成了ResultSet的迭代(取代了Spring JDBC的迭代处理)
					List<AssignMeterDataStatisExtendQuery> amdStatisExtendList = mapper.mapResultSet2Object(rs, AssignMeterDataStatisExtendQuery.class);
					if(amdStatisExtendList!=null && amdStatisExtendList.size()>0) {
						for(AssignMeterDataStatisExtendQuery amdStatisExtend: amdStatisExtendList) {
							extendQueryMap.put(amdStatisExtend.getMeterDataStatisUID(), amdStatisExtend);
						}
					}
				} catch (Exception e) {
					logger.error(e.getMessage());
					throw new SQLException(e.getMessage());
				}
			}
		});
		return extendQueryMap;
	}
	
	/**
	 * @return the iResourceAccessControlDao
	 */
	public IResourceAccessControlDao getiResourceAccessControlDao() {
		return iResourceAccessControlDao;
	}

	/**
	 * @param iResourceAccessControlDao the iResourceAccessControlDao to set
	 */
	public void setiResourceAccessControlDao(
			IResourceAccessControlDao iResourceAccessControlDao) {
		this.iResourceAccessControlDao = iResourceAccessControlDao;
	}
	
}
